﻿<!doctype html>
<html>
<head>
    <title>SpreadJS - Basic Data Validator</title>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />

    <link href="../../../external/spreadjs/css/gcspread.sheets.excel2013white.9.40.20161.0.css" rel="stylesheet" type="text/css" />

    <script src="../../../external/external/jquery-1.8.2.min.js" type="text/javascript"></script>

    <script type="text/javascript" src="../../../external/spreadjs/gcspread.sheets.all.9.40.20161.0.min.js"></script>

    <script id="scriptInit" type="text/javascript">
        /*code_begin*/
        var spreadNS = GcSpread.Sheets;
        $(document).ready(function () {
            var spread = new GcSpread.Sheets.Spread(document.getElementById("ss"), { sheetCount: 2 });
            initSpread(spread);
        });

        function initSpread(spread) {
            var sheet = spread.getSheet(0);
            sheet.isPaintSuspended(true);
            loadData(spread);
            setValidator(spread);
            sheet.isPaintSuspended(false);

            $("#highlightInvalidData").prop("checked", spread.highlightInvalidData());

            $("#highlightInvalidData").click(function () {
                spread.highlightInvalidData($("#highlightInvalidData").prop("checked"));
            });
        };
        function loadData(spread) {
            var sheet = spread.getActiveSheet();

            sheet.setRowHeight(3, 40);
            sheet.setValue(3, 0, "Shopping Place");
            var title = sheet.getCell(3, 0);
            title.font("bold 20px arial");
            title.vAlign(spreadNS.VerticalAlign.center);
            title.backColor("#D1CBC5");
            sheet.setColumnWidth(0, 160);
            sheet.setColumnWidth(1, 35);
            sheet.setBorder(new spreadNS.Range(3, 0, 3, 1), new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), { all: true });
            sheet.setValue(4, 0, "Wal-Mart");
            sheet.setValue(5, 0, "Other");
            sheet.getCell(4, 0).font("bold 15px arial");
            sheet.getCell(5, 0).font("bold 15px arial");
            var startRow = 3;
            var startCol = 1;
            sheet.addSpan(startRow + 0, startCol + 0, 1, 4);
            sheet.setRowHeight(startRow + 0, 40);
            sheet.setValue(startRow + 0, startCol + 0, "Goods List");
            var title = sheet.getCell(startRow + 0, startCol + 0);
            title.font("bold 30px arial");
            title.vAlign(spreadNS.VerticalAlign.center);
            title.backColor("#D1CBC5");

            sheet.setColumnWidth(startCol + 0, 100);
            sheet.setColumnWidth(startCol + 1, 100);
            sheet.setColumnWidth(startCol + 2, 100);
            sheet.setColumnWidth(startCol + 3, 120);
            sheet.setBorder(new spreadNS.Range(startRow + 0, startCol + 0, 8, 4), new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), { all: true });

            sheet.setValue(startRow + 1, startCol + 0, "Name");
            sheet.setValue(startRow + 1, startCol + 1, "Category");
            sheet.setValue(startRow + 1, startCol + 2, "Price");
            sheet.setValue(startRow + 1, startCol + 3, "Shopping Place");
            for (var i = 0; i < 4; i++) {
                sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial");
            }

            sheet.setValue(startRow + 2, startCol + 0, "Apple");
            sheet.setValue(startRow + 3, startCol + 0, "Potato");
            sheet.setValue(startRow + 4, startCol + 0, "Tomato");
            sheet.setValue(startRow + 5, startCol + 0, "Sandwich");
            sheet.setValue(startRow + 6, startCol + 0, "Hamburger");
            sheet.setValue(startRow + 7, startCol + 0, "Grape");

            sheet.setValue(startRow + 2, startCol + 1, "Fruit");
            sheet.setValue(startRow + 3, startCol + 1, "Vegetable");
            sheet.setValue(startRow + 4, startCol + 1, "Vegetable");
            sheet.setValue(startRow + 5, startCol + 1, "Food");
            sheet.setValue(startRow + 6, startCol + 1, "Food");
            sheet.setValue(startRow + 7, startCol + 1, "Fruit");

            sheet.setValue(startRow + 2, startCol + 2, 1.00);
            sheet.setValue(startRow + 3, startCol + 2, 2.01);
            sheet.setValue(startRow + 4, startCol + 2, 3.21);
            sheet.setValue(startRow + 5, startCol + 2, 2);
            sheet.setValue(startRow + 6, startCol + 2, 2);
            sheet.setValue(startRow + 7, startCol + 2, 4);
            var myFormatter = new spreadNS.GeneralFormatter("$#,##0.00;[Red] $#,##0.00", spreadNS.FormatMode.CustomMode);
            for (var i = 2; i < 8; i++) {
                sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter);
            }

            sheet.setValue(startRow + 2, startCol + 3, "Wal-Mart");
            sheet.setValue(startRow + 3, startCol + 3, "Other");
            sheet.setValue(startRow + 4, startCol + 3, "Other");
            sheet.setValue(startRow + 5, startCol + 3, "Wal-Mart");
            sheet.setValue(startRow + 6, startCol + 3, "Wal-Mart");
            sheet.setValue(startRow + 7, startCol + 3, "Other");
            //set invalid data
            sheet.setValue(6, 2, "sss");
            sheet.setValue(10, 4, "Sun Store");
        }

        function setValidator(spread) {
            var sheet = spread.getActiveSheet();

            spread.highlightInvalidData(true);

            //ListValidator
            var dv1 = new spreadNS.DefaultDataValidator.createListValidator("Fruit,Vegetable,Food");
            dv1.inputTitle = "Please choose a category:";
            dv1.inputMessage = "Fruit<br/>Vegetable<br/>Food";
            for (var i = 5; i < 11; i++) {
                sheet.setDataValidator(i, 2, dv1);
            }

            //FormulaListValidator
            var dv2 = new spreadNS.DefaultDataValidator.createFormulaListValidator("A5:A6");
            for (var i = 5; i < 11; i++) {
                sheet.setDataValidator(i, 4, dv2);
            }
        }
        /*code_end*/
    </script>

</head>
<body>
<div class="sample-turtorial">
    <div id="ss" style="width:100%; height:400px;border: 1px solid gray;"></div>
    <div class="demo-options">
        <div class="option-row">
            <input type="checkbox" id="highlightInvalidData"/>
            <label for="highlightInvalidData">Highlight invalid data</label>
        </div>
    </div>
</div>
</body>
</html>
